﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using University.Workshop.Model;
using System.Globalization;
namespace University.Workshop.DataAccess
{
    public static class ProductDataAccess
    {
        const string ConexionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Pharmacy;Integrated Security=True";

        public static bool AddProduct(Product product)
        {
            string sqlSentence = "INSERT INTO Product(Code,Name,PresentationCode,Provider,Admission,State,Description,RecetMedic)VALUES (@code,@name,@presentation,@provaider,@admission,@state,@description,@recetmedic)";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (product == null) 
            {
                throw new BusinessException("se quiso ingresar un producto nulo");

            }
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", product.CodeProduct);
                cmd.Parameters.AddWithValue("@name", product.NameProduct);
                cmd.Parameters.AddWithValue("@presentation", product.Presentation);
                cmd.Parameters.AddWithValue("@provaider", product.CodeProvider);          
                cmd.Parameters.AddWithValue("@admission", product.DateAdmission);              
                cmd.Parameters.AddWithValue("@state", product.State);
                cmd.Parameters.AddWithValue("@description", product.Description);
                cmd.Parameters.AddWithValue("@recetmedic", product.PrescriptionMedic);
                result = cmd.ExecuteNonQuery();

                

                return result != 0;
                
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool ValidateProduct(string code)
        {
            string sqlSentence = "SELECT Code FROM Product WHERE Code = @code";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", code);
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                    return true;
                else
                    return false;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static DataTable LookProducts(string value)
        {
            string sqlSentence = "SELECT P.Code, P.Name, PRE.Description,PR.Name, P.Admission ,P.Description, P.RecetMedic FROM Product P, Presentation PRE, Provider PR WHERE  P.PresentationCode = PRE.Code and P.Provider = PR.Nit and P.Code =  @code";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = null;
            DataTable tableAux = null;
            try
            {
                table = new DataTable();
                table.Locale = CultureInfo.InvariantCulture;
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", value);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                table.Load(reader);
                tableAux = table;
                return tableAux;
            }
            finally
            {
                
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
             
            } 
        }

        public static bool EditProductData(Product products)
        {
            string sqlSentence = "UPDATE Product SET Name = @name, PresentationCode = @presentation,Provider = @provaider,Admission = @admission,State = @state , Description = @description , RecetMedic = @recetmedic WHERE Code = @code";
            
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (products == null)
            {
                throw new BusinessException("se quiso ingresar un producto vacio");
            }
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", products.CodeProduct);
                cmd.Parameters.AddWithValue("@name", products.NameProduct);
                cmd.Parameters.AddWithValue("@presentation", products.Presentation);
                cmd.Parameters.AddWithValue("@provaider", products.CodeProvider);
                cmd.Parameters.AddWithValue("@admission", products.DateAdmission);
                cmd.Parameters.AddWithValue("@state", products.State);
                cmd.Parameters.AddWithValue("@description", products.Description);
                cmd.Parameters.AddWithValue("@recetmedic", products.PrescriptionMedic);
                result = cmd.ExecuteNonQuery();
                if (result == 0)
                {

                    return false;
                }
                else
                {
                    return true;
                }
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool DeleteProductData(string value)
        {
            string sqlSentence = "UPDATE Product SET State = 'I' WHERE Code = @code";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", value);
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                    return true;
                else
                    return false;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool EnableProducts(string value) 
        {
            string sqlSentence = "UPDATE Product SET State = 'A' WHERE Code = @code";
            
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", value);
                SqlDataReader reader = cmd.ExecuteReader();

                return !reader.HasRows;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static DataColumn GenerateQuery1()
        {
            string sqlSentence = "SELECT p.Name , s.Quantity From Product p , Stock s Where p.Code=s.CodeProduct ";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataColumn table = new DataColumn();
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(reader);
                table = dt.Columns[0];
                return dt.Columns[0];
            }
            finally
            {

                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();

            }
        }

        public static DataColumn GenerateQuery2()
        {
            string sqlSentence = "SELECT s.Quantity From Stock s, Product p Where s.CodeProduct=p.Code";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataColumn table = new DataColumn();
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(reader);
                table = dt.Columns[0];
                return table;
            }
            finally
            {

                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();

            }
        }

        public static DataTable QuantityAndPrice(string code)
        {
            string sqlSentence = "SELECT Quantity,Pricesale FROM Stock WHERE CodeProduct = @code";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = null;
            try
            {
                table = new DataTable();
                table.Locale = CultureInfo.InvariantCulture;
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", code);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                table.Load(reader);
                return table;
            }
            finally
            {

                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();

            }

        }

        public static DataTable PresentationProduct(string code)
        {
            string sqlSentence = "SELECT p.Description FROM Presentation p WHERE p.Code = @code";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = null;
            try
            {
                table = new DataTable();
                table.Locale = CultureInfo.InvariantCulture;
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", code);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                table.Load(reader);
                return table;
            }
            finally
            {

                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();

            }
        }

        public static DataTable ReportProductAscendent()
        {
            string sqlSentence = "SELECT p.Name, p.code, s.Quantity FROM Product p, Stock s WHERE p.Code = s.CodeProduct ORDER BY s.Quantity";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = null;
            try
            {
                table = new DataTable();
                table.Locale = CultureInfo.InvariantCulture;
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                table.Load(reader);
                return table;
            }
            finally
            {

                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();

            }
        }
    }
}
